# 导入日志包并配置日志
import logging
import pymysql

class DBUtils:
    def __init__(self, host: str, user: str, password: str, database: str, port: int = 3306, charset: str = 'utf8'):
        self.host = host
        self.port = port
        self.user = user
        self.password = password
        self.database = database
        self.charset = charset
        self.db = None
        self.cursor = None

    # 封装获取连接和游标的方法
    def __get_cursor(self):
        db = pymysql.connect(
            host=self.host,
            port=self.port,
            user=self.user,
            password=self.password,
            database=self.database,
            charset=self.charset
        )
        logging.info(f"连接数据库成功: {db}")
        self.db = db
        self.cursor = db.cursor()

    # 封装插入操作
    def insert(self, sql: str, params: tuple or str or None = None) -> int:
        try:
            self.__get_cursor()
            with self.db and self.cursor:
                if isinstance(params, list):
                    self.cursor.executemany(sql, params)
                else:
                    self.cursor.execute(sql, params)
                self.db.commit()
                return self.cursor.lastrowid
        except Exception as e:
            # 记录更多上下文信息
            logging.error(f"执行 SQL 时出错: {sql}, 参数: {params}, 错误信息: {e}")


    # 封装修改操作
    def update(self, sql: str, params: tuple or str or None=None) -> int:
        try:
            self.__get_cursor()
            with self.db and self.cursor:
                if isinstance(params, list):
                    self.cursor.executemany(sql, params)
                else:
                    self.cursor.execute(sql, params)
                self.db.commit()
                return self.cursor.rowcount
        except Exception as e:
            # 记录更多上下文信息
            logging.error(f"执行 SQL 时出错: {sql}, 参数: {params}, 错误信息: {e}")

    # 封装删除操作
    def delete(self, sql: str, params: tuple or str or None=None) -> int:
        try:
            self.__get_cursor()
            with self.db and self.cursor:
                if isinstance(params, list):
                    self.cursor.executemany(sql, params)
                else:
                    self.cursor.execute(sql, params)
                self.db.commit()
                return self.cursor.rowcount
        except Exception as e:
            # 记录更多上下文信息
            logging.error(f"执行 SQL 时出错: {sql}, 参数: {params}, 错误信息: {e}")


    # 封装查询操作
    def query(self, sql: str, params: tuple or str or None=None, many: int=0) -> tuple:
        try:
            self.__get_cursor()
            with self.db and self.cursor:
                self.cursor.execute(sql, params)
                return self.cursor.fetchall() if many == 0 else self.cursor.fetchmany(many)
        except Exception as e:
            # 记录更多上下文信息
            logging.error(f"执行 SQL 时出错: {sql}, 参数: {params}, 错误信息: {e}")


    # 封装关闭连接和游标的方法
    def __del__(self):
        if self.cursor:
            self.cursor.close()
        if self.db:
            self.db.close()